package system.report;

import java.io.IOException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;

import system.base.ProductCategory;
import system.base.Warehouse;
import system.report.pojo.ReportYKProductMonthPojo;

import com.hitrust.gs.lib.GSAutoAuditongDateConfigurator;
import com.hitrust.gs.lib.GSBaseConstants;

import framework.core.ConstDefine;
import framework.core.DBConnection;
import framework.core.DBEngine;
import framework.core.DBPreparedStatement;
import framework.core.DBResult;
import framework.core.LogWriter;
import framework.core.ObjectList;
import framework.core.StringUtil;

public class ReportYKProductMonth {
	
	public void doReport(String title1,String warehouseName,String month,String warehouseCode,String start,String end,String title,String fileName,HttpServletRequest request,HttpServletResponse response,boolean isAvaliable,boolean isLager,String reportYKProductMonthEnd,String querytype){
		if(GSBaseConstants.PLANPRICE.equals("true")){
			title1 = warehouseName + "仓库产品进、销、存资金汇总月报";
		}
		else{
			title1 = warehouseName + "仓库压库产品进、销、存资金汇总月报";
		}
		String all = getReportYKProductMonthSql(warehouseCode, start, end,title, reportYKProductMonthEnd);

		Map vec = new HashMap();

		DBConnection con = null;
		DBResult dbrs = null;
		java.sql.ResultSet rs = null;
		try {
			con = DBConnection.open(false);
			dbrs = DBEngine.doSelect(con, all);
			rs = dbrs.getRs();
			while (rs.next()) {
				String[] values = new String[9];
				values[0] = rs.getString("category_code");
				values[1] = rs.getString("init_count");
				values[2] = rs.getString("init_money");
				values[3] = rs.getString("in_count");
				values[4] = rs.getString("in_money");
				values[5] = rs.getString("out_count");
				values[6] = rs.getString("out_money");
				values[7] = rs.getString("final_count");
				values[8] = rs.getString("final_money");
				vec.put(values[0],values);
			}
			String date = "";
			if(querytype.equals("1")){
				date = "统计月:" + start.substring(0, 7); // 显示日期
			}else{
				date = "统计月:" + start.substring(0, 10) + " 至 " + end.substring(0, 10); // 显示日期
			}
			if (vec.size() == 0) {
				isAvaliable = false;
			} else if (vec.size() > 13000) {
				isAvaliable = false;
				isLager = true;
			} else {
				writeExcel_ReportYKProductMonth(vec, warehouseCode, month, title, fileName, title1, date, request, response, start, end);
			}
		} catch (Exception e2) {
			System.out.println(e2.getMessage());
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (dbrs != null) {
					dbrs.clear();
				}
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {

				e.printStackTrace();
			} catch (Exception e) {

				e.printStackTrace();
			}
		}
	}
	
	public List findReportByWarehouseCode(String warehouseCode){
		DBConnection con = null;
		DBResult dbrs = null;
		java.sql.ResultSet rs = null;
		List list = new ArrayList();
		try {
			con = DBConnection.open(false);
			String all = "select  * from t_reportykproductmonth  where warehouse_code = '"+warehouseCode+"'";
			dbrs = DBEngine.doSelect(con, all);
			rs = dbrs.getRs();
			while (rs.next()) {
				String[] values = new String[9];
				values[0] = rs.getString("category_code");
				values[1] = rs.getString("init_count");
				values[2] = rs.getString("init_money");
				values[3] = rs.getString("in_count");
				values[4] = rs.getString("in_money");
				values[5] = rs.getString("out_count");
				values[6] = rs.getString("out_money");
				values[7] = rs.getString("final_count");
				values[8] = rs.getString("final_money");
				list.add(values);
			}
		}catch (Exception e) {
				LogWriter.error(e.getMessage());
		}finally{
			try {
				if (rs != null) {
					rs.close();
				}
				if (dbrs != null) {
					dbrs.clear();
				}
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {

				e.printStackTrace();
			} catch (Exception e) {

				e.printStackTrace();
			}
		}
		return list;
	}

	private String getReportYKProductMonthSql(String warehouseCode,
			String start, String end, String title, String reportYKProductMonth) {
		// 期初分类查询入库查询不包括"申沃"产品分类部分
		String monthSotragePartIn1 = " select "
			+ " substr(category_code, 1, 1) || '0000' category_code, "
			+ " sum(product_count) init_count, "
			+ " sum(product_money) init_money "
			+ " from "
			+ " t_in_sub insub "
			+ " left outer join t_in_head inhead on insub.in_bill_id = inhead.id "
			+ " left outer join t_product product on insub.product_id = product.id "
			+ " where " + " 1 = 1 " + " and inhead.warehouse_code like '" + warehouseCode + "%' " + " and inhead.in_time < '"+ start +"' and inhead.in_time > '"+reportYKProductMonth+"'" 
			+ " and insub.CREATE_TIME < '"+start+"'and insub.CREATE_TIME > '"+reportYKProductMonth+"'"
			+ " and product.category_code <> '90001' " + " and insub.balance_mode = '" + ConstDefine.getBalanceModeByFlag() + "' and insub.status = '1'" + " group by " + " substr(category_code, 1, 1) ";

		// 期初分类查询入库查询只包括"申沃"产品分类部分
		String monthStoragePartIn2 = " select "
			+ " category_code, "
			+ " sum(product_count) init_count, "
			+ " sum(product_money) init_money "
			+ " from "
			+ " t_in_sub insub "
			+ " left outer join t_in_head inhead on insub.in_bill_id = inhead.id "
			+ " left outer join t_product product on insub.product_id = product.id "
			+ " where " + " 1 = 1 " + " and inhead.warehouse_code like '" + warehouseCode + "%' " + " and inhead.in_time < '" + start  +"' and inhead.in_time > '"+reportYKProductMonth+"'"
			+ " and insub.CREATE_TIME < '"+start+"'and insub.CREATE_TIME > '"+reportYKProductMonth+"'"
			+ " and product.category_code = '90001' " + " and insub.balance_mode = '" + ConstDefine.getBalanceModeByFlag() + "' and insub.status = '1'" + " group by " + " category_code ";

		// 期初分类查询出库查询不包括"申沃"产品分类部分
		String monthSotragePartOut1 = " select "
			+ " substr(category_code, 1, 1) || '0000' category_code, "
			+ " sum(product_count) * -1 init_count, "
			+ " sum(product_money) * -1 init_money "
			+ " from "
			+ " t_out_sub outsub "
			+ " left outer join t_out_head outhead on outsub.out_bill_id = outhead.id "
			+ " left outer join t_product product on outsub.product_id = product.id "
			+ " where " + " 1 = 1 " + " and outhead.warehouse_code like '" + warehouseCode + "%' " + " and outhead.out_time < '"+ start +"' and outhead.out_time > '"+reportYKProductMonth+"'"
			+ " and outsub.CREATE_TIME < '"+start+"' and outsub.CREATE_TIME > '"+reportYKProductMonth+"'"
			+ " and product.category_code <> '90001' " + " and outsub.balance_mode = '" + ConstDefine.getBalanceModeByFlag() + "' and outsub.status = '1'" + " group by " + " substr(category_code, 1, 1) ";

		// 期初分类查询出库查询只包括"申沃"产品分类部分
		String monthStoragePartOut2 = " select "
			+ " category_code, "
			+ " sum(product_count) * -1 init_count, "
			+ " sum(product_money) * -1 init_money "
			+ " from "
			+ " t_out_sub outsub "
			+ " left outer join t_out_head outhead on outsub.out_bill_id = outhead.id "
			+ " left outer join t_product product on outsub.product_id = product.id "
			+ " where " + " 1 = 1 " + " and outhead.warehouse_code like '" + warehouseCode + "%' " + " and outhead.out_time < '" + start + "' " +" and outhead.out_time > '"+reportYKProductMonth+"'"
			+ " and outsub.CREATE_TIME < '"+start+"' and outsub.CREATE_TIME > '"+reportYKProductMonth+"'"
			+ " and product.category_code = '90001' " + " and outsub.balance_mode = '" + ConstDefine.getBalanceModeByFlag() + "' and outsub.status = '1'" + " group by " + " category_code ";


		// 入库查询子查询不包括"90001"部分
		String inPart1 = " select " + " substr(category_code, 1, 1) || '0000' category_code, " + " sum(insub.product_count) in_count, "
		+ " sum(insub.product_money) in_money " + " from " + " t_in_sub insub "
		+ " left outer join t_in_head inhead on insub.in_bill_id = inhead.id "
		+ " left outer join t_product product on insub.product_id = product.id " + " where " + " 1 = 1 " + " and inhead.warehouse_code like '" + warehouseCode + "%' " + " and inhead.in_time >= '" + start + "' " + " and inhead.in_time <= '" + end + "' "
		+ " and insub.CREATE_TIME <= '"+end+"' and insub.CREATE_TIME >= '"+start+"'"
		+ " and product.category_code <> '90001' " + " and insub.balance_mode = '" + ConstDefine.getBalanceModeByFlag() + "' and insub.status = '1'" + " group by " + " substr(category_code, 1, 1) ";

		// 入库查询子查询只包括"90001"部分
		String inPart2 = " select " + " category_code, " + " sum(insub.product_count) in_count, " + " sum(insub.product_money) in_money " + " from "
		+ " t_in_sub insub " + " left outer join t_in_head inhead on insub.in_bill_id = inhead.id "
		+ " left outer join t_product product on insub.product_id = product.id " + " where " + " 1 = 1 " + " and inhead.warehouse_code like '" + warehouseCode + "%' " + " and inhead.in_time >= '" + start + "' " + " and inhead.in_time <= '" + end + "' "
		+ " and insub.CREATE_TIME <= '"+end+"' and insub.CREATE_TIME >= '"+start+"'"
		+ " and product.category_code = '90001' " + " and insub.balance_mode = '" + ConstDefine.getBalanceModeByFlag() + "' and insub.status = '1'" + " group by " + " category_code ";


		// 出库查询子查询不包括90001部分
		String outPart1 = " select " + " substr(category_code, 1, 1) || '0000' category_code, " + " sum(outsub.product_count) out_count, "
		+ " sum(outsub.product_money) out_money " + " from " + " t_out_sub outsub "
		+ " left outer join t_out_head outhead on outsub.out_bill_id = outhead.id "
		+ " left outer join t_product product on outsub.product_id = product.id " + " where " + " 1 = 1 " + " and outhead.warehouse_code like '" + warehouseCode + "%' " + " and outhead.out_time >= '" + start + "' " + " and outhead.out_time <= '" + end + "' "
		+ " and outsub.CREATE_TIME >= '"+start+"' and outsub.CREATE_TIME<= '"+end+"'"
		+ " and product.category_code <> '90001' " + " and outsub.balance_mode = '" + ConstDefine.getBalanceModeByFlag() + "' and outsub.status = '1'" + " group by substr(category_code, 1, 1) ";

		// 出库查询子查询只包括90001部分
		String outPart2 = " select " + " category_code, " + " sum(outsub.product_count) out_count, " + " sum(outsub.product_money) out_money " + " from "
		+ " t_out_sub outsub " + " left outer join t_out_head outhead on outsub.out_bill_id = outhead.id "
		+ " left outer join t_product product on outsub.product_id = product.id " + " where " + " 1 = 1 " + " and outhead.warehouse_code like '" + warehouseCode + "%' " + " and outhead.out_time >= '" + start + "' " + " and outhead.out_time <= '" + end + "' "
		+ " and outsub.CREATE_TIME >= '"+start+"' and outsub.CREATE_TIME<= '"+end+"'"
		+ " and product.category_code = '90001' " + " and outsub.balance_mode = '" + ConstDefine.getBalanceModeByFlag() + "' and outsub.status = '1'" + " group by category_code ";

		// 合计期初数据
		String sql_a3 = "";
		if("3".equals(title)){
			sql_a3 = "select " +
			"a3.category_code, " +
			"sum(a3.init_count) init_count , " +
			"sum(a3.init_money) init_money, " +
			"0 in_count, " +
			"0 in_money, " +
			"0 out_count, " +
			"0 out_money " +
			"from( " +
			monthSotragePartIn1 + " union all " +
			monthStoragePartIn2 + " union all " +
			monthSotragePartOut1 + " union all " +
			monthStoragePartOut2 +
			" ) a3 group by a3.category_code ";
		}
		String sql_a1 = "select a1.category_code, " +
		"0 init_count, " +
		"0 init_money, " +
		"a1.in_count, " +
		"a1.in_money, " +
		"0 out_count, " +
		"0 out_money " +
		"from( " +
		inPart1 + " union all" + inPart2 +
		") a1 ";

		String sql_a2 = "select a2.category_code, " +
		"0 init_count, " +
		"0 init_money, " +
		"0 in_count, " +
		"0 in_money, " +
		"a2.out_count, " +
		"a2.out_money " +
		"from( " +
		outPart1 + " union all " + outPart2 +
		" ) a2 ";


		String all = "select " +
		" aa1.category_code, " +
		" sum(aa1.init_count) init_count, " +
		" sum(aa1.init_money) init_money, " +
		" sum(aa1.in_count) in_count, " +
		" sum(aa1.in_money) in_money, " +
		" sum(aa1.out_count) out_count, " +
		" sum(aa1.out_money) out_money, " +
		" sum(aa1.init_count) + sum(aa1.in_count) - sum(aa1.out_count) final_count, " +
		" sum(aa1.init_money) + sum(aa1.in_money) - sum(aa1.out_money) final_money " +
		" from ( " +
		sql_a3 + " union all " + sql_a1 + " union all " + sql_a2 +
		") aa1 group by category_code order by category_code";
		return all;
	}
	
	private static String getReportYKProductMonthSqlByInsert(String warehouseCode,
			String start, String end, String title) {
		// 期初分类查询入库查询不包括"申沃"产品分类部分
		String monthSotragePartIn1 = " select "
			+ " substr(category_code, 1, 1) || '0000' category_code, "
			+ " sum(product_count) init_count, "
			+ " sum(product_money) init_money "
			+ " from "
			+ " t_in_sub insub "
			+ " left outer join t_in_head inhead on insub.in_bill_id = inhead.id "
			+ " left outer join t_product product on insub.product_id = product.id "
			+ " where " + " 1 = 1 " + " and inhead.warehouse_code like '" + warehouseCode + "%' " + " and inhead.in_time < '"+ start +"'"
			+ " and product.category_code <> '90001' " + " and insub.balance_mode = '" + ConstDefine.getBalanceModeByFlag() + "' and insub.status = '1'" + " group by " + " substr(category_code, 1, 1) ";

		// 期初分类查询入库查询只包括"申沃"产品分类部分
		String monthStoragePartIn2 = " select "
			+ " category_code, "
			+ " sum(product_count) init_count, "
			+ " sum(product_money) init_money "
			+ " from "
			+ " t_in_sub insub "
			+ " left outer join t_in_head inhead on insub.in_bill_id = inhead.id "
			+ " left outer join t_product product on insub.product_id = product.id "
			+ " where " + " 1 = 1 " + " and inhead.warehouse_code like '" + warehouseCode + "%' " + " and inhead.in_time < '" + start+"'"
			+ " and product.category_code = '90001' " + " and insub.balance_mode = '" + ConstDefine.getBalanceModeByFlag() + "' and insub.status = '1'" + " group by " + " category_code ";

		// 期初分类查询出库查询不包括"申沃"产品分类部分
		String monthSotragePartOut1 = " select "
			+ " substr(category_code, 1, 1) || '0000' category_code, "
			+ " sum(product_count) * -1 init_count, "
			+ " sum(product_money) * -1 init_money "
			+ " from "
			+ " t_out_sub outsub "
			+ " left outer join t_out_head outhead on outsub.out_bill_id = outhead.id "
			+ " left outer join t_product product on outsub.product_id = product.id "
			+ " where " + " 1 = 1 " + " and outhead.warehouse_code like '" + warehouseCode + "%' " + " and outhead.out_time < '"+ start+"'"
			+ " and product.category_code <> '90001' " + " and outsub.balance_mode = '" + ConstDefine.getBalanceModeByFlag() + "' and outsub.status = '1'" + " group by " + " substr(category_code, 1, 1) ";

		// 期初分类查询出库查询只包括"申沃"产品分类部分
		String monthStoragePartOut2 = " select "
			+ " category_code, "
			+ " sum(product_count) * -1 init_count, "
			+ " sum(product_money) * -1 init_money "
			+ " from "
			+ " t_out_sub outsub "
			+ " left outer join t_out_head outhead on outsub.out_bill_id = outhead.id "
			+ " left outer join t_product product on outsub.product_id = product.id "
			+ " where " + " 1 = 1 " + " and outhead.warehouse_code like '" + warehouseCode + "%' " + " and outhead.out_time < '" + start + "' "
			+ " and product.category_code = '90001' " + " and outsub.balance_mode = '" + ConstDefine.getBalanceModeByFlag() + "' and outsub.status = '1'" + " group by " + " category_code ";


		// 入库查询子查询不包括"90001"部分
		String inPart1 = " select " + " substr(category_code, 1, 1) || '0000' category_code, " + " sum(insub.product_count) in_count, "
		+ " sum(insub.product_money) in_money " + " from " + " t_in_sub insub "
		+ " left outer join t_in_head inhead on insub.in_bill_id = inhead.id "
		+ " left outer join t_product product on insub.product_id = product.id " + " where " + " 1 = 1 " + " and inhead.warehouse_code like '" + warehouseCode + "%' " + " and inhead.in_time >= '" + start + "' " + " and inhead.in_time <= '" + end + "' "
		+ " and product.category_code <> '90001' " + " and insub.balance_mode = '" + ConstDefine.getBalanceModeByFlag() + "' and insub.status = '1'" + " group by " + " substr(category_code, 1, 1) ";

		// 入库查询子查询只包括"90001"部分
		String inPart2 = " select " + " category_code, " + " sum(insub.product_count) in_count, " + " sum(insub.product_money) in_money " + " from "
		+ " t_in_sub insub " + " left outer join t_in_head inhead on insub.in_bill_id = inhead.id "
		+ " left outer join t_product product on insub.product_id = product.id " + " where " + " 1 = 1 " + " and inhead.warehouse_code like '" + warehouseCode + "%' " + " and inhead.in_time >= '" + start + "' " + " and inhead.in_time <= '" + end + "' "
		+ " and product.category_code = '90001' " + " and insub.balance_mode = '" + ConstDefine.getBalanceModeByFlag() + "' and insub.status = '1'" + " group by " + " category_code ";


		// 出库查询子查询不包括90001部分
		String outPart1 = " select " + " substr(category_code, 1, 1) || '0000' category_code, " + " sum(outsub.product_count) out_count, "
		+ " sum(outsub.product_money) out_money " + " from " + " t_out_sub outsub "
		+ " left outer join t_out_head outhead on outsub.out_bill_id = outhead.id "
		+ " left outer join t_product product on outsub.product_id = product.id " + " where " + " 1 = 1 " + " and outhead.warehouse_code like '" + warehouseCode + "%' " + " and outhead.out_time >= '" + start + "' " + " and outhead.out_time <= '" + end + "' "
		+ " and product.category_code <> '90001' " + " and outsub.balance_mode = '" + ConstDefine.getBalanceModeByFlag() + "' and outsub.status = '1'" + " group by substr(category_code, 1, 1) ";

		// 出库查询子查询只包括90001部分
		String outPart2 = " select " + " category_code, " + " sum(outsub.product_count) out_count, " + " sum(outsub.product_money) out_money " + " from "
		+ " t_out_sub outsub " + " left outer join t_out_head outhead on outsub.out_bill_id = outhead.id "
		+ " left outer join t_product product on outsub.product_id = product.id " + " where " + " 1 = 1 " + " and outhead.warehouse_code like '" + warehouseCode + "%' " + " and outhead.out_time >= '" + start + "' " + " and outhead.out_time <= '" + end + "' "
		+ " and product.category_code = '90001' " + " and outsub.balance_mode = '" + ConstDefine.getBalanceModeByFlag() + "' and outsub.status = '1'" + " group by category_code ";

		// 合计期初数据
		String sql_a3 = "";
		if("3".equals(title)){
			sql_a3 = "select " +
			"a3.category_code, " +
			"sum(a3.init_count) init_count , " +
			"sum(a3.init_money) init_money, " +
			"0 in_count, " +
			"0 in_money, " +
			"0 out_count, " +
			"0 out_money " +
			"from( " +
			monthSotragePartIn1 + " union all " +
			monthStoragePartIn2 + " union all " +
			monthSotragePartOut1 + " union all " +
			monthStoragePartOut2 +
			" ) a3 group by a3.category_code ";
		}
		String sql_a1 = "select a1.category_code, " +
		"0 init_count, " +
		"0 init_money, " +
		"a1.in_count, " +
		"a1.in_money, " +
		"0 out_count, " +
		"0 out_money " +
		"from( " +
		inPart1 + " union all" + inPart2 +
		") a1 ";

		String sql_a2 = "select a2.category_code, " +
		"0 init_count, " +
		"0 init_money, " +
		"0 in_count, " +
		"0 in_money, " +
		"a2.out_count, " +
		"a2.out_money " +
		"from( " +
		outPart1 + " union all " + outPart2 +
		" ) a2 ";


		String all = "select " +
		" aa1.category_code, " +
		" sum(aa1.init_count) init_count, " +
		" sum(aa1.init_money) init_money, " +
		" sum(aa1.in_count) in_count, " +
		" sum(aa1.in_money) in_money, " +
		" sum(aa1.out_count) out_count, " +
		" sum(aa1.out_money) out_money, " +
		" sum(aa1.init_count) + sum(aa1.in_count) - sum(aa1.out_count) final_count, " +
		" sum(aa1.init_money) + sum(aa1.in_money) - sum(aa1.out_money) final_money " +
		" from ( " +
		sql_a3 + " union all " + sql_a1 + " union all " + sql_a2 +
		") aa1 group by category_code order by category_code";
		return all;
	}

	/**
	 * 压库产品进销存资金汇总月报
	 * 
	 * @param aList
	 * @param title
	 * @param start
	 * @param end
	 */
	private void writeExcel_ReportYKProductMonth(Map vec, String warehouseCode, String month, String title, String fileName, String title1, String date,
			HttpServletRequest request, HttpServletResponse response, String start, String end) {
		ServletOutputStream out = null;
		try {
			HSSFWorkbook tWb = new HSSFWorkbook();
			HSSFSheet tSheet = tWb.createSheet();
			HSSFRow tRow = null;
			Region region = null;
			tSheet.setColumnWidth((short) 0, (short) (16 * 256));
			tSheet.setColumnWidth((short) 1, (short) (12 * 256));
			tSheet.setColumnWidth((short) 2, (short) (12 * 256));
			tSheet.setColumnWidth((short) 3, (short) (18 * 256));
			tSheet.setColumnWidth((short) 4, (short) (10 * 256));

			tWb.setSheetName(0, title, HSSFCell.ENCODING_UTF_16);

			HSSFFont font = tWb.createFont();
			font.setFontHeightInPoints((short) 9);
			font.setFontName("宋体");
			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

			HSSFCellStyle cStyle = tWb.createCellStyle();
			cStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
			HSSFCellStyle cStyleM = tWb.createCellStyle();
			cStyleM.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			cStyleM.setFont(font);

			HSSFCellStyle cStyleM_R = tWb.createCellStyle();
			cStyleM_R.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
			cStyleM_R.setFont(font);

			// 标题行
			tRow = tSheet.createRow((short) 0);
			HSSFCell c0 = createCell(tWb, tRow, (short) 2, title1);
			c0.setCellStyle(cStyleM);
			// 日期行
			tRow = tSheet.createRow((short) 1);
			region = new Region(1, (short) 3, 1, (short) 4);
			tSheet.addMergedRegion(region);
			createCell(tWb, tRow, (short) 3, date, HSSFCellStyle.ALIGN_CENTER, false);

			// 表头行
			tRow = tSheet.createRow((short) 2);

			HSSFCell c1 = createCell(tWb, tRow, (short) 0, "产品类别");
			HSSFCell c2 = createCell(tWb, tRow, (short) 1, "期初金额");
			HSSFCell c3 = createCell(tWb, tRow, (short) 2, "入库金额");
			HSSFCell c4 = createCell(tWb, tRow, (short) 3, "出库金额");
			HSSFCell c5 = createCell(tWb, tRow, (short) 4, "期末金额");

			c1.setCellStyle(cStyleM_R);
			c2.setCellStyle(cStyleM_R);
			c3.setCellStyle(cStyleM_R);
			c4.setCellStyle(cStyleM_R);
			c5.setCellStyle(cStyleM_R);
			short index = 2; // 当前所在行号
			DBConnection con = null;
			con = DBConnection.open(false);
			double totalMonth = 0;
			double totalIn = 0;
			double totalOut = 0;
			double totalFinal = 0;
			List list = this.findReportByWarehouseCode(warehouseCode);
			for (int i = 0; i < list.size(); i++) {
				String[] vaList = (String[]) list.get(i);
				String[] values =  (vec.get(vaList[0])==null?new String[]{vaList[0],"0","0","0","0","0","0","0","0"}:(String[])vec.get(vaList[0]));
				// 創建一行
				tRow = tSheet.createRow((short) (index + 1));
				String cateName = null;
				if (values[0].equals("000000")) {
					cateName = "未分类";
				} else {
					ProductCategory pc = ProductCategory.fetchByCode(con, values[0]);
					if (pc != null) {
						cateName = pc.getName();
					}
				}
				double monthStorageMoneyTempDouble = Double.parseDouble(values[2])+Double.parseDouble(vaList[8]);
				double inMoneyTempDouble = Double.parseDouble(values[4]);
				double outMoneyTempDouble = Double.parseDouble(values[6]);
				double finalMoneyTempDouble = Double.parseDouble(values[8]) + Double.parseDouble(vaList[8]);
				String monthStorageMoneyTemp = String.valueOf(monthStorageMoneyTempDouble);
				String inMoneyTemp = String.valueOf(inMoneyTempDouble);
				String outMoneyTemp = String.valueOf(outMoneyTempDouble);
				String finalMoneyTemp = String.valueOf(finalMoneyTempDouble);
				createCell(tWb, tRow, (short) 0, cateName);
				createCell(tWb, tRow, (short) 1, StringUtil.showPrice(monthStorageMoneyTemp));
				createCell(tWb, tRow, (short) 2, StringUtil.showPrice(inMoneyTemp));
				createCell(tWb, tRow, (short) 3, StringUtil.showPrice(outMoneyTemp));
				createCell(tWb, tRow, (short) 4, StringUtil.showPrice(finalMoneyTemp));
				index++;
				totalMonth += Double.parseDouble(monthStorageMoneyTemp);
				totalIn += Double.parseDouble(inMoneyTemp);
				totalOut += Double.parseDouble(outMoneyTemp);
				totalFinal += Double.parseDouble(finalMoneyTemp);
			}
			// 总计行
			if (index > 2) {
				tRow = tSheet.createRow((short) (index + 2));
				HSSFCell zongjiCell = tRow.createCell((short) 0);
				HSSFCell cell_1 = tRow.createCell((short) 1);
				HSSFCell cell_2 = tRow.createCell((short) 2);
				HSSFCell cell_3 = tRow.createCell((short) 3);
				HSSFCell cell_4 = tRow.createCell((short) 4);
				zongjiCell.setCellStyle(cStyleM_R);
				cell_1.setCellStyle(cStyleM_R);
				cell_2.setCellStyle(cStyleM_R);
				cell_3.setCellStyle(cStyleM_R);
				cell_4.setCellStyle(cStyleM_R);
				zongjiCell.setEncoding(HSSFCell.ENCODING_UTF_16);
				zongjiCell.setCellValue("合计：");
				cell_1.setCellValue(StringUtil.showPrice(String.valueOf(totalMonth)));
				cell_2.setCellValue(StringUtil.showPrice(String.valueOf(totalIn)));
				cell_3.setCellValue(StringUtil.showPrice(String.valueOf(totalOut)));
				cell_4.setCellValue(StringUtil.showPrice(String.valueOf(totalFinal)));
			}
			fileName = fileName + ".xls";
			response.setContentType("application/ms-excel");
			response.addHeader("Content-disposition", "filename=" + fileName);
			out = response.getOutputStream();
			tWb.write(out);
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (out != null) {
				try {
					out.close();
				} catch (IOException e) {

					e.printStackTrace();
				}
			}
		}
	}
	
	private HSSFCell createCell(HSSFWorkbook aWb, HSSFRow aRow, short column, String alaValueue) {
		HSSFCell tCell = aRow.createCell(column);
		tCell.setEncoding(HSSFCell.ENCODING_UTF_16);
		tCell.setCellValue(alaValueue);
		return tCell;
	}
	
	private HSSFCell createCell(HSSFWorkbook aWb, HSSFRow aRow, short column, String aValue, short aAlign, boolean border) {
		HSSFCell tCell = aRow.createCell(column);
		tCell.setEncoding(HSSFCell.ENCODING_UTF_16);
		tCell.setCellValue(aValue);
		HSSFCellStyle tCellStyle = aWb.createCellStyle();
		tCellStyle.setAlignment(aAlign);
		tCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		if (border) {
			tCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			tCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			tCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
			tCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
			tCellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
			tCellStyle.setTopBorderColor(HSSFColor.BLACK.index);
			tCellStyle.setRightBorderColor(HSSFColor.BLACK.index);
			tCellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
		}
		tCell.setCellStyle(tCellStyle);
		return tCell;
	}
	
	public static void insertDataByReportYKProductMonth(){
		DBConnection con = null;
		DBPreparedStatement db = null;
		DBResult result = null;
		String start = "";
		String end = "";
		try {
			GSAutoAuditongDateConfigurator.getInstance();
			start = GSAutoAuditongDateConfigurator.getReportYKProductMonthStart();
			end = GSAutoAuditongDateConfigurator.getReportYKProductMonthEnd();
		} catch (Exception e1) {
			LogWriter.error(e1.getMessage());
		}
		try {
			con = DBConnection.open(false);
			con.setAutoCommit(false);
			String sql = "delete from t_reportykproductmonth";
			db = con.prepareStatement(sql);
			db.executeUpdate();
			ObjectList warehouseList =Warehouse.fetchWarehouses(con, "00");
			for (int i = 0; i < warehouseList.size(); i++) {
				Warehouse w = (Warehouse) warehouseList.get(i);
				String all = getReportYKProductMonthSqlByInsert(w.getCode(), start, end,"3");	
				result = DBEngine.doSelect(con, all);
				java.sql.ResultSet rs  = result.getRs();
				while(rs.next()){
					String insert = "insert into t_reportykproductmonth values (" +
							" '" +rs.getString("CATEGORY_CODE")+"',"+
							" " +rs.getString("INIT_COUNT")+","+
							" " +rs.getString("INIT_MONEY")+","+
							" " +rs.getString("IN_COUNT")+","+
							" " +rs.getString("IN_MONEY")+","+
							" " +rs.getString("OUT_COUNT")+","+
							" " +rs.getString("OUT_MONEY")+","+
							" " +rs.getString("FINAL_COUNT")+","+
							" " +rs.getString("FINAL_MONEY")+","+
							" '"+w.getCode()+("'")+
							" )";
					db = con.prepareStatement(insert);
					db.executeQuery();
				}
			}
			con.commit();
		} catch (Exception e) {
			e.printStackTrace();
			LogWriter.error(e.getMessage());
		}finally{
			try {
            	if(db!=null){
            		db.close();
            	}
            	if (con != null){
                    con.close();
                } 
            	if(result!=null){
            		result.clear();
            	}
            } catch (Exception e) {
                e.printStackTrace();
                LogWriter.error(e.getMessage());
            }
		}
	}
	
	public ReportYKProductMonthPojo findReportByWarehouseCodeAndCategoryCode(String warehouseCode,String categoryCode){
		DBConnection con = null;
		DBPreparedStatement db = null;
		DBResult result = null;
		ReportYKProductMonthPojo pojo = null;
		try {
			con = DBConnection.open(false);
			String sql = "select * from t_reportykproductmonth where category_code ='"+categoryCode+"' and warehouse_code = '"+warehouseCode+"'";
			result = DBEngine.doSelect(con, sql);
			java.sql.ResultSet rs  = result.getRs();
			LogWriter.info(sql);
			if(rs.next()){
				pojo = new ReportYKProductMonthPojo();
				pojo.categoryCode = rs.getString("category_code") == null?"":rs.getString("category_code");
				pojo.initCount = rs.getDouble("init_count");
				pojo.initMoney = rs.getDouble("init_money");
				pojo.inCount = rs.getDouble("in_count");
				pojo.inMoney = rs.getDouble("in_money");
				pojo.outCount = rs.getDouble("out_count");
				pojo.outMoney = rs.getDouble("out_Money");
				pojo.finalCount = rs.getDouble("final_count");
				pojo.finalMoney = rs.getDouble("final_money");
				pojo.warehouseCode = rs.getString("warehouse_code") == null ?"":rs.getString("warehouse_code");
				return pojo;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(result!=null){
				try {
					result.clear();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			if(con!=null){
				try {
					con.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
		return pojo;
	}
}
